package model;

import globals.SystemGeneralState;
import gui.mainframe.MainFrame;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.Collections;
import java.util.LinkedList;
import java.util.List;

import javax.swing.JOptionPane;

import org.apache.log4j.Logger;
import org.h2.tools.Server;

public class DatabaseCommons {

	/*
	 * logger
	 */
	private final static Logger logger = Logger.getLogger(MainFrame.class);

	private Connection con;
	private String[] args = { "" };
	Server server;

	public DatabaseCommons() {
		con = null;
		server = null;
	}

	public void startH2Server() {
		System.out.println("Starting H2 Server");
		try {
			server = Server.createTcpServer(null).start();
			System.out.println("***** Status: " + server.getStatus()
					+ "\nURL: " + server.getURL());
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void stopH2Server() {
		if (server != null) {
			System.out.println("Stopping H2 Server");
			server.stop();
		}
	}

	public Connection getConnection() {
		try {
			if (con == null) {
				h2Connect();
			} else if (con.isClosed()) {
				logger.info("Connection '" + con.toString() + "' closed? "
						+ con.isClosed());
				// System.out.println("Connection '" + con.toString() +
				// "' closed? " + con.isClosed());
				con = null;
				// System.out.println("1 - con=null: " + con.toString());
				h2Connect();
				logger.info("Connection '" + con.toString()
						+ "' restarted. Now closed? " + con.isClosed());
				// System.out.println("Connection '" + con.toString()
				// + "' restarted. Now closed? " + con.isClosed());
			}
		} catch (SQLException e) {
			logger.error("SQLException@getConnection: " + e);
			// System.out.println("SQLException@getConnection: " + e);
		}
		return con;
	}

	public void h2Connect() throws SQLException {
		// checks the SQL connector
		try {
			Class.forName("org.h2.Driver");
		} catch (ClassNotFoundException e) {
			// System.out
			// .println("ClassNotFoundException while seeking the H2 driver: "
			// + e);
			logger.error("ClassNotFoundException while seeking the H2 driver: "
					+ e);
		}
		// create connection for 'embedded' mode.
		String url = "jdbc:h2:~/test", sqlUname = "sa", sqlPass = "";
		/*
		 * In order for the application to run it's own instance of the H2
		 * server. I have created the startH2Server and StopH2Server methods to
		 * run it within the application. Also, the type of server is changed
		 * from the 'embedded' to the 'server' mode.
		 */

		// changing url from embedded to server mode
		url = "jdbc:h2:tcp://localhost/~/test";
		// sqlPass = "r00t";

		// if connection is already created, just return
		if (con != null)
			return;
		// otherwise, create it
		con = DriverManager.getConnection(url, sqlUname, sqlPass);
		// System.out.println("Connected: " + con);

	}

	public void h2Reconnect() {

	}

	public void disconnect() throws SQLException {
		logger.info("...db connection closing now.");
//		System.out.println("...db connection closing now.");
		if (con != null) {
			con.close();
			logger.info("isClosed? " + con.isClosed());
			//System.out.println("isClosed? " + con.isClosed());
		}
	}

//	public void setAutoCommit(Boolean boolAutocommit, Connection connection) {
//		String esql, csql;
//		int col = 0, colClient = 0;
//		boolean isSuccessful = false;
//		/*
//		 * id will be used to check the id's while I go through the elements
//		 * reps to compare occurrences
//		 */
//		int id = -1, reps = -1, lastId = -1;
//		/*
//		 * sql queries with question marks are used in prepared statements since
//		 * they are more secure due to being more difficult to be attacked by a
//		 * code injection tactic.
//		 */
//		if (boolAutocommit) {
//			esql = "set autocommit TRUE";
//		} else {
//			esql = "set autocommit FALSE";
//		}
//		csql = "select autocommit()";
//		PreparedStatement eps = null, qps = null;
//		ResultSet rs = null;
//		con = getConnection();
//		try {
//			// h2Connect();
//			qps = connection.prepareStatement(esql);
//			// eps.setBoolean(1, boolAutocommit);
//			qps.executeUpdate();
//
//			qps = connection.prepareStatement(csql);
//			rs = qps.executeQuery();
//
//			if (rs != null && rs.next()) {
//				isSuccessful = rs.getBoolean(1);
//			}
//
//			System.out.println("setAutocommit to: " + boolAutocommit
//					+ " successful? " + (isSuccessful == boolAutocommit));
//
//			// disconnect();
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//
//	}

	public int getLastInsertedIndex(PreparedStatement eps, String methodCalling) {
		int generatedId = -1;
		try {
			ResultSet rsGeneratedKey = eps.getGeneratedKeys();

			if (rsGeneratedKey != null && rsGeneratedKey.next()) {
				generatedId = rsGeneratedKey.getInt(1);
			}
		} catch (SQLException e) {
			logger.error("SQLException@getLastInsertedId: " + e);
//			System.out.println("SQLException@getLastInsertedId: " + e);
		}
		logger.info(methodCalling + " generated last id: " + generatedId);
		// System.out
		// .println(methodCalling + " generated last id: " + generatedId);
		return generatedId;
	}
}
